# -*- coding=utf-8 -*-

import logging
import pymysql
import config

class SqlHelper(object):
    def __init__(self, db_name = config.database_name, db_config = config.database_config):
        self.conn = pymysql.connect(**db_config)
        self.cursor = self.conn.cursor()
        self.conn.select_db(db = db_name)
        self.conn.autocommit(False)

        self.db_name = db_name

    def create_database(self, database_name):
        try:
            command = 'CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARACTER SET \'utf8\' ' % database_name
            # logging.debug('sql helper create_database command:%s' % command)
            self.cursor.execute(command)
        except Exception as e:
            logging.exception('sql helper create_database exception:%s' % str(e))

    def create_table(self, command):
        try:
            # logging.debug('sql helper create_table command:%s' % command)
            self.cursor.execute(command)
            self.conn.commit()
        except Exception as e:
            logging.exception('sql helper create_table exception:%s' % str(e))

    def select_db(self, db_name):
        self.conn.select_db(db_name)
        self.db_name = db_name

    def insert_data(self, command, data, commit = False):
        try:
            # logging.debug('insert_data command:%s, data:%s' % (command, data))

            self.cursor.execute(command, data)
            if commit:
                self.conn.commit()
        except Exception as e:
            logging.exception('sql helper insert_data exception msg:%s' % e, logging.WARNING)

    def insert_json(self, data = {}, table_name = None, commit = False):
        try:
            keys = []
            vals = []
            for k, v in data.items():
                keys.append(k)
                vals.append(v)
            val_str = ','.join(['%s'] * len(vals))
            key_str = ','.join(keys)

            command = "INSERT IGNORE INTO {table} ({keys}) VALUES({values})". \
                format(keys = key_str, values = val_str, table = table_name)
            # logging.debug('insert_json command:%s' % command)
            self.cursor.execute(command, tuple(vals))

            if commit:
                self.conn.commit()
        except Exception as e:
            logging.exception('sql helper insert_json exception msg:%s' % e)

    def update_json(self, datas, condition, table_name = None, commit = False, ):
        try:
            fields = ""
            for i, (key, val) in enumerate(datas.items()):
                fields += '{key}=\"{val}\"'.format(key = key, val = val)
                if i < len(datas.items()) - 1:
                    fields += ','

            cond_str = ''
            for i, (key, val) in enumerate(condition.items()):
                cond_str = '{key}=\'{val}\''.format(key = key, val = val)
                if i < len(condition.items()) - 1:
                    cond_str += ' and '

            command = '''UPDATE {table_name} SET {fields} WHERE {condition};'''.format(
                table_name = table_name, fields = fields, condition = cond_str)
            self.cursor.execute(command)

            print(command)

            if commit:
                self.conn.commit()
        except Exception as e:
            logging.exception('sql helper update_json exception msg:%s' % e)

    def replace_json(self, data={}, table_name=None, commit=False):
        try:
            keys = []
            vals = []
            for k, v in data.items():
                keys.append(k)
                vals.append(v)
            val_str = ','.join(['%s'] * len(vals))
            key_str = ','.join(keys)

            command = "REPLACE INTO {table} ({keys}) VALUES({values})". \
                format(keys=key_str, values=val_str, table=table_name)
            self.cursor.execute(command, tuple(vals))

            if commit:
                self.conn.commit()
            return True
        except Exception as e:
            logging.exception('sql helper insert_json exception msg:%s' % e)
            return False

    def insert_json_list(self, datas, table_name = None, commit = False):
        try:
            if len(datas) < 0:
                return
            data = datas[0]
            keys = list(data.keys())
            values = [list(data.values()) for data in datas]

            self.insert_list(keys, values, table_name, commit)
        except Exception as e:
            logging.exception('sql helper insert_json_list exception msg:%s' % e)

    # keys = ['name', 'age'] values = [('liu', 10), ('guang', 20'), ('quan', 30)]
    def insert_list(self, keys, values, table_name = None, commit = False):
        try:
            key_param = ','.join(keys)
            val_param = ','.join(['%s'] * len(keys))

            command = "INSERT IGNORE INTO {table_name} ({keys}) values({val})". \
                format(table_name = table_name, keys = key_param, val = val_param)

            self.cursor.executemany(command, values)

            if commit:
                self.conn.commit()
        except Exception as e:
            logging.exception('sql helper insert_json exception msg:%s' % e)

    def commit(self):
        self.conn.commit()

    def execute(self, command, commit = True, args = None):
        try:
            # logging.debug('sql helper execute command:%s' % command)
            data = self.cursor.execute(command, args)
            self.conn.commit()
            return data
        except Exception as e:
            logging.exception('sql helper execute exception msg:%s' % str(e))
            return None

    def is_exists(self, table_name):
        try:
            command = "SHOW TABLES LIKE '%s'" % table_name
            logging.debug('sql helper is_exists command:%s' % command)
            data = self.cursor.execute(command)
            return True if data == 1 else False
        except Exception as e:
            logging.exception('sql helper is_exists exception msg:%s' % e)

    def query(self, command, commit = False, cursor_type = 'tuple'):
        try:
            logging.debug('sql helper execute command:%s' % command)

            cursor = None
            if cursor_type == 'dict':
                cursor = self.conn.cursor(pymysql.cursors.DictCursor)
            else:
                cursor = self.cursor

            cursor.execute(command)
            data = cursor.fetchall()
            if commit:
                self.conn.commit()
            return data
        except Exception as e:
            logging.exception('sql helper execute exception msg:%s' % str(e))
            return None

    def query_one(self, command, commit = False, cursor_type = 'tuple'):
        try:
            logging.debug('sql helper execute command:%s' % command)

            cursor = None
            if cursor_type == 'dict':
                cursor = self.conn.cursor(pymysql.cursors.DictCursor)
            else:
                cursor = self.cursor

            cursor.execute(command)
            data = cursor.fetchone()
            if commit:
                self.conn.commit()
            return data
        except Exception as e:
            logging.exception('sql helper execute exception msg:%s' % str(e))
            return None

    def check_table_exists(self, table_name, db_name = None):
        if db_name == None:
            db_name = self.db_name

        command = """SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '{db_name}' AND table_name = '{table_name}'""".format(
            db_name = db_name, table_name = table_name)
        self.cursor.execute(command)
        try:
            if self.cursor.fetchone()[0] == 1:
                return True
        except:
            pass

        return False
